#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlError>
#include <QDebug>
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    initTable();
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_insertAct_triggered()
{
#if 0
    /* 准备sql*/
    QString sql = QString("insert into yk  values('%1',%2);").
            arg(ui->nameLineEdit->text()).
            arg(ui->ageLineEdit->text().toInt());
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    /* 执行sql*/
   if(!query.exec(sql))
   {
       ui->statusBar->showMessage(query.lastError().text());
   }
#endif
   int crow = ui->tableWidget->rowCount();
   ui->tableWidget->insertRow(crow);
}

void MainWindow::on_delAct_triggered()
{
#if 0
    /* 准备sql*/
    QString sql = "delete from yk where age = ? and name = ?;";
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,ui->ageLineEdit->text().toInt());
    query.bindValue(1,ui->nameLineEdit->text());
    /* 执行sql*/
    if(!query.exec())
    {
        ui->statusBar->showMessage(query.lastError().text());
    }
#endif
   QString name = ui->tableWidget->currentItem()->text();
   QSqlQuery query;
   query.prepare("delete from yk where name = ?;");
   query.bindValue(0,name);
   if(!query.exec())
   {
       ui->statusBar->showMessage(query.lastError().text());
   }
}

void MainWindow::on_updateAct_triggered()
{
    /* 准备sql*/
    QString sql = "update yk set age=? WHERE name = ?;";
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,ui->ageLineEdit->text().toInt());
    query.bindValue(1,ui->nameLineEdit->text());
    /* 执行sql*/
    if(!query.exec())
    {
        ui->statusBar->showMessage(query.lastError().text());
    }
}

void MainWindow::on_batchInsertAct_triggered()
{
    /* 准备sql*/
    QString sql = QString("insert into yk  values(?,?);");
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    query.prepare(sql);
    QVariantList nameList; nameList<<"aaa"<<"bbb"<<"ccc";
    QVariantList ageList;  ageList<<1<<2<<3;
    query.addBindValue(nameList);
    query.addBindValue(ageList);
    /* 执行sql*/
   if(!query.execBatch())
   {
       ui->statusBar->showMessage(query.lastError().text());
   }
}

void MainWindow::initTable()
{
    QString sql = QString("SELECT * from yk;");
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    query.exec(sql);

    while(query.next())
    {
//        qDebug()<<query.value(0)<<","<<query.value("age");
        int crow = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow(crow);
        ui->tableWidget->setItem(crow,0,new QTableWidgetItem(query.value(0).toString()));
        ui->tableWidget->setItem(crow,1,new QTableWidgetItem(query.value(1).toString()));
    }
}

void MainWindow::on_submitAct_triggered()
{
    QString name = ui->tableWidget->item(ui->tableWidget->currentRow(),0)->text();
    QString age = ui->tableWidget->item(ui->tableWidget->currentRow(),1)->text();
    QString sql = QString("insert into yk  values('%1',%2);").
            arg(name).
            arg(age);
    /* 实例化QsqlQuery 对象*/
    QSqlQuery query;
    /* 执行sql*/
   if(!query.exec(sql))
   {
       ui->statusBar->showMessage(query.lastError().text());
   }
}

void MainWindow::on_refreshAct_triggered()
{
    ui->tableWidget->clearContents();
    ui->tableWidget->setRowCount(0);
    initTable();
}
